import pymysql
import uuid
import random
import json

mysql_conf = {
    "host": "localhost",
    "user": "root",
    "password": "qwe369",
    "db": "exercise"
}

con_local = pymysql.connect(**mysql_conf)

# 实际操作都需要光标进行
cursor_local = con_local.cursor()


def sql_1_data_generator():
    CREATE_SQL = """
            CREATE TABLE my_order(
            id int primary key auto_increment,
            order_id varchar(255),
            store_name varchar(255),
            amount decimal(10, 2),
            create_time datetime default CURRENT_TIMESTAMP
            );
    """
    # cursor_local.execute(CREATE_SQL)
    SQL = """insert into my_order(order_id, store_name, amount) values (%s, %s, %s)"""
    # args = [(str(uuid.uuid1()), f"store_{random.randint(0, 16)}", format(random.random()*1000, ".2f"))
    args = [(str(uuid.uuid1()), None, None)
            for i in range(0, 20)]
    cursor_local.executemany(SQL, args)
    con_local.commit()


def sql_2_data_generator():
    CREATE_SQL = """
                CREATE TABLE depart_store (
                id int primary key auto_increment,
                store_name varchar(255),
                department varchar(255)
                );
    """
    cursor_local.execute(CREATE_SQL)
    # 创建store_1~store_14的店铺列表
    store_array = [f"store_{i}" for i in range(15)]
    # 打乱store_array顺序
    random.shuffle(store_array)
    print(store_array)

    SQL = """insert into depart_store(department, store_name) values (%s, %s)"""
    for i in range(5):
        # 按间距选取3个store
        tmp_store_array = store_array[i::5]
        args = [(f"depart_{i}", f"{store_name}") for store_name in tmp_store_array]
        cursor_local.executemany(SQL, args)
    con_local.commit()


if __name__ == "__main__":
    sql_1_data_generator()
    # sql_2_data_generator()